package com.dp.service.impl3;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.dp.pojo.JsonPage;
import com.dp.pojo.PictureContent;

import com.dp.pub.SysProperty;
import com.dp.util.BaseBean;
import com.dp.util.BookConfig;
import com.dp.util.DataMap;
import com.dp.util.ImageScalee;
import com.dp.util.InterUtil;
import com.dp.vo.NewsResources;
import com.dp.vo.TGenre;

@Service
@Transactional
public class PictureServiceImpl3 extends BaseImpl {
	private static Logger log = Logger.getLogger(PictureServiceImpl3.class);
	
	public int findAllPictureContentCount(DataMap hashMap) {
		String roleid=(String)hashMap.get("roleid");
		String userid=(String)hashMap.get("userid");
		String userids=(String)hashMap.get("userids");
		String picname=(String)hashMap.get("picname");
		String genreid=(String)hashMap.get("genreid");
		String status=(String)hashMap.get("status");
		String channelid=(String)hashMap.get("channelid");
		 sql = "SELECT count(tp.contentid) FROM t_service_info p,t_picture tp left join t_genre tg on tg.genre_id=tp.genre_id";
		if (channelid != null && !"".equals(channelid)) {
			sql += ",news_contenttitle nc,news_extends_channel_title nect,news_extends_channel nec where";
			sql += " nec.channel_id='"+channelid+"' and nc.contentid=tp.contentid and nc.cid=nect.cid and nect.channleid=nec.channleid";
			sql += " and p.service_code=tp.service_code";
		}
		else
			sql += " where p.service_code=tp.service_code";

		//权限
		if(StringIsNotNull(roleid) && StringIsNotNull(userid))
		{
			sql = sql + " AND tp.user_id='" + userid + "'";
		}else if(userids!=null){
			sql = sql + " AND tp.user_id in (" + userids + ")";
		}
		if (picname != null && !"".equals(picname)) {
			sql += " and tp.content_name like '%" + picname + "%'";
		}
		if (genreid != null && !"".equals(genreid)) {
			sql += " and tp.genre_id = '" + genreid + "'";
		}
		if(status != null && !"".equals(status)) {
			sql += " and tp.content_status = '" + status + "'";
		}
		int count = 0;
		try {
			log.info(" findAllPictureContentCount excute sql: " +sql);
			count =this.getOneInt();
		} catch (Exception e) {
			log.info(" findAllPictureContentCount fail !!!" +e.getMessage());
			e.printStackTrace();
		}
		return count;
	}
	
	public List<PictureContent> findAllPictureContent(DataMap hashMap, int firstrow, int maxrow) {
		JsonPage jpage=(JsonPage)hashMap.get("jpage");
		String roleid=(String)hashMap.get("roleid");
		String userid=(String)hashMap.get("userid");
		String userids=(String)hashMap.get("userids");
		String picname=(String)hashMap.get("picname");
		String genreid=(String)hashMap.get("genreid");
		String status=(String)hashMap.get("status");
		String channelid=(String)hashMap.get("channelid");
		String sql = "SELECT tg.genre_name,p.price,tp.contentid,tp.user_id,tp.service_code,tp.genre_id,tp.content_name,tp.content_keyword,tp.content_pubdate,tp.conten_info,tp.content_updateddate,tp.Copyright_deadline,tp.content_status,tp.audituser,tp.pub_company,tp.cause" +
				" FROM t_service_info p,t_picture tp left join t_genre tg on tg.genre_id=tp.genre_id";
		if (channelid != null && !"".equals(channelid)) {
			sql += ",news_contenttitle nc,news_extends_channel_title nect,news_extends_channel nec where";
			sql += " nec.channel_id='"+channelid+"' and nc.contentid=tp.contentid and nc.cid=nect.cid and nect.channleid=nec.channleid";
			sql += " and p.service_code=tp.service_code";
		}
		else
			sql += " where p.service_code=tp.service_code";

		//权限
		if(StringIsNotNull(roleid) && StringIsNotNull(userid))
		{
			sql = sql + " AND tp.user_id='" + userid + "'";
		}else if(userids!=null){
			sql = sql + " AND tp.user_id in (" + userids + ")";
		}
		if (picname != null && !"".equals(picname)) {
			sql += " and tp.content_name like '%" + picname + "%'";
		}
		if (genreid != null && !"".equals(genreid)) {
			sql += " and tp.genre_id = '" + genreid + "'";
		}
		if(status != null && !"".equals(status)) {
			sql += " and tp.content_status = '" + status + "'";
		}
		if (jpage != null && jpage.getSidx() != null
				&& !"".equals(jpage.getSidx())) {
			sql += " order by " + jpage.getSidx();
			sql += " " + jpage.getSord();
		}
		log.info(" findAllPictureContent excute sql: " +sql);
		List<Object[]> list = this.getNativeQuery(sql, firstrow, maxrow);
		List<PictureContent> result = null;
		if (list.size() > 0) 
		{
			result = new ArrayList<PictureContent>(list.size());
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			for (Object[] objects : list) 
			{
				if (objects != null && objects.length > 0) 
				{
					int j = 0;
					PictureContent one = new PictureContent();
					one.setGenrename((String)objects[j++]);
					one.setPrice((Integer)objects[j++]);
					one.setContentid((String)objects[j++]);
					one.setUser_id((String)objects[j++]);
					one.setServicecode((String)objects[j++]);
					one.setGenre_id((String)objects[j++]);
					one.setContent_name((String)objects[j++]);
					one.setContent_keyword((String)objects[j++]);
					one.setContent_pubdate(sdf.format((Date)objects[j++]));
					one.setConten_info((String)objects[j++]);
					one.setContent_updateddate(sdf.format((Date)objects[j++]));
					one.setCopyright_deadline(sdf.format((Date)objects[j++]));
					one.setContent_status((String)objects[j++]);
					one.setAudituser((String)objects[j++]);
					one.setPub_company((String)objects[j++]);
					one.setCause((String)objects[j++]);
					result.add(one);
				}
			}
		}
		return result;
	}
	
	public boolean subAudit(String content_id_list)
	{
		String sql = "update t_picture set content_status='auditing',content_updateddate=now() where contentid in ("+content_id_list+")";
		try {
			log.info(" subAudit excute sql :" +sql);
            return execUpdate(sql);
        } catch (Exception e) {
        	log.info(" subAudit fail !!!" +e.getMessage());
            return false;
        }
	}
	
	public boolean auditPicture(String content_id_list, String user_id)
	{
		
		//从复选框取得contentidlist
		ArrayList<String> sqlList = new ArrayList<String>();
		String updateSql = "update t_picture set content_status='pass',audituser='"+user_id+ "',cause=null,content_updateddate=now() where contentid in ("+content_id_list+")";
		sqlList.add(updateSql);
		log.info(" auditPicture excute batch updateSql:" +updateSql);
		String[] c_id_strList=content_id_list.split(",");
		try {
        	for(String contentId:c_id_strList)
        	{
        		//审核时生成内容icon 
        		String resourceid = this.scaleImage(contentId);
        		if(resourceid == null)
	            	return false;
        		else if(resourceid!=null && !resourceid.equals(""))
	            {
        			String iconrid=this.queryOne("select max(n.rid) rid from news_resources n where n.name='"+resourceid+"'");
	            	String upConIconSql = "update news_contenttitle set contenticon="+iconrid+" where contentid="+contentId;
	            	sqlList.add(upConIconSql);
	            	log.info(" auditPicture excute batch upConIconSql:" +upConIconSql);
	            }
        		Object cid = this.getSingleResult("select cid from NewsContenttitle where contentid="+contentId);
				String tmpsql = "update news_extends_channel_title set contentstatus=2 where cid=" +cid;
				sqlList.add(tmpsql);
				log.info(" auditPicture excute batch upChTitleSql:" +tmpsql);
			} 
        	return this.execTransaction(sqlList);
		} catch (Exception e) {
			log.info(" auditPicture fail !!!" +e.getMessage());
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean unpassPicture(String content_id_list, String cause, String user_id)
	{
		String updateSql = "update t_picture set content_status='unpass',audituser='"+user_id+ "',cause='"+cause+"',content_updateddate=now() where contentid in ("+content_id_list+")";
		log.info(" unpassPicture excute updateSql:" +updateSql);
		try {
			return execUpdate(updateSql);
		} catch (Exception e) {
			log.info(" unpassPicture fail !!!" +e.getMessage());
			e.printStackTrace();
			return false;
		}
	}
	
	public String scaleImage(String contentid)
	{
		String sql = "select nr.rid,nr.useragents,nr.rformat,nr.fullpath from news_resources nr,news_contenresource ncr,news_contenttitle nc" +
				" where nr.resourcetype=1 and nr.rid=ncr.rid and ncr.cid=nc.cid and nc.contentid="+contentid;
		String rid = "";
		List resList = null;
		try {
			resList = this.queryALLResultSet(sql);
		
			if(resList.size()>0)
			{
				ImageScalee ims = new ImageScalee();
				HashMap<String,String> tmpMap = (HashMap)resList.get(0);
				rid = tmpMap.get("rid").toString();
				String fullpath = tmpMap.get("fullpath");
				//由于图片存在在硬盘是以filename_size.format形式，所以用useragents确定图片大小
				//转换图片时用filefullpath:filename_size.format，为缩略图命名和插入数据库还用原来的fullpath
				String size = tmpMap.get("useragents")+".";
				String filefullpath = fullpath.replace(".", size);
				String imageSizeScaleStr = SysProperty.getPrep("image_imagesizescale");
	        	String imageFormatScaleStr = SysProperty.getPrep("image_imageformatscale");
	        	if(imageFormatScaleStr == null || imageFormatScaleStr.equals(""))
	        		imageFormatScaleStr = "png";
				String[] imageSizeScaleAry = imageSizeScaleStr.split(","); 
				String[] imageFormatScaleAry = imageFormatScaleStr.split(","); 
				if(imageSizeScaleAry != null&&imageSizeScaleAry.length>0)
				{
					for(String imageSizeScale:imageSizeScaleAry)
					{
						String screenWidHei = imageSizeScale.split("@")[0];
						String picWidHei = imageSizeScale.split("@")[1];
						int screenWidth = Integer.parseInt(screenWidHei.split("x")[0]);
						int screenHeight = Integer.parseInt(screenWidHei.split("x")[1]);
						int width = Integer.parseInt(picWidHei.split("x")[0]);
						int height = Integer.parseInt(picWidHei.split("x")[1]);
						if(imageFormatScaleAry!=null && imageFormatScaleAry.length>0)
							for(String imageFormatScale:imageFormatScaleAry)
								ims.compressPicsScale(filefullpath, width, height, imageFormatScale, screenWidth, screenHeight,fullpath);
					}
				}
				String scaleFullPath = fullpath.replace(".", "-p.");
				sql = "insert into news_resources (name,newformat,fullpath) values('"+rid+"','"+imageFormatScaleStr+"','"+scaleFullPath+"')";
				this.execUpdate(sql);
			}
		} catch (Exception e) {
			log.info(" convert content image fail !!!" +e.getMessage());
			e.printStackTrace();
			return null;
		}
		return rid;
	}
	
	public boolean insertPicture(PictureContent one,String channelids)
	{
		BaseBean b = new BaseBean();
		ArrayList<String> sqllist = new ArrayList<String>();
		String contentid = b.ObjectToStringDB(one.getContentid());
		String user_id = b.ObjectToStringDB(one.getUser_id());
		String service_code = b.ObjectToStringDB(one.getServicecode());
		String genre_id = b.ObjectToStringDB(one.getGenre_id());
		String content_name = b.ObjectToStringDB(one.getContent_name());
		String content_keyword = b.ObjectToStringDB(one.getContent_keyword());
		String content_pubdate = b.ObjectToStringDB(one.getContent_pubdate());
		String conten_info = b.ObjectToStringDB(one.getConten_info());
		String Copyright_deadline = b.ObjectToStringDB(one.getCopyright_deadline());
		String pub_company = b.ObjectToStringDB(one.getPub_company()); 
		
		StringBuffer inPicSql = new StringBuffer("insert into t_picture (contentid,user_id,service_code,genre_id,content_name,content_keyword,");
		inPicSql.append("content_pubdate,conten_info,content_updateddate,Copyright_deadline,content_status,pub_company) values(");
		inPicSql.append(contentid+",");
		inPicSql.append(user_id+",");
		inPicSql.append(service_code+",");
		inPicSql.append(genre_id+",");
		inPicSql.append(content_name+",");
		inPicSql.append(content_keyword+",");
		inPicSql.append(content_pubdate+",");
		inPicSql.append(conten_info+",");
		inPicSql.append("now(),");
		inPicSql.append(Copyright_deadline+",");
		inPicSql.append("'new',");
		inPicSql.append(pub_company+")");
		sqllist.add(inPicSql.toString());
		log.info(" insertPicture excute batch sql 1:" +inPicSql.toString());
		inPicSql.delete(0, inPicSql.length());
		String contenttext = "'"+InterUtil.rb.getString("src.pic.title")+":"+one.getContent_name()+"" +
						"@@"+InterUtil.rb.getString("src.pic.time")+":"+new SimpleDateFormat("yyyy-MM-dd").format(new Date())+"" +
						"@@"+InterUtil.rb.getString("src.explain")+":"+one.getConten_info()+"" +
						"@@"+InterUtil.rb.getString("src.source")+":"+one.getPub_company()+"'";
		String contenticon = SysProperty.getPrep("sysiconid_title");
		if(contenticon == null || contenticon.equals(""))
        	contenticon = "1";
		inPicSql.append("insert into news_contenttitle (contentid,contenttitle,contenticon,contenttext,contentsource,newstype) values (");
		inPicSql.append(contentid+",");
		inPicSql.append(content_name+",");
		inPicSql.append(contenticon+",");
		inPicSql.append(contenttext+",");
		inPicSql.append(pub_company+",");
		//从图片内容开始,news_contenttitle中newstype直接使用约定的英文
		inPicSql.append("'image')");
		sqllist.add(inPicSql.toString());
		log.info(" insertPicture excute batch sql 2:" +inPicSql.toString());
		inPicSql.delete(0, inPicSql.length());
		String [] channelid=channelids.split(",");
		String tempSql="insert into news_extends_channel_title(channleid,cid,contentstatus,istest,isnew)VALUES(";
		for(int i=0;i<channelid.length;i++)
        {
			//图片内容没有测试状态
			int isNeedTest = 1;
			//是否需要测试,true为是,false为否
//			String needtest = SysProperty.getPrep("news-needtest");
//			if(needtest!=null && needtest.equals("false"))
//				isNeedTest = 0;
			String tmp = tempSql+"(select channleid from news_extends_channel where channel_id='"+channelid[i]+"'),(select LAST_INSERT_ID()),0,"+isNeedTest+",1)";
			sqllist.add(tmp);
			log.info(" insertPicture excute batch sql 3:" +tmp);
        }
		
		try {
			return execTransaction(sqllist);
		} catch (Exception e) {
			log.info(" insertPicture fail !!!" +e.getMessage());
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean insertPicResource(NewsResources newsResources,String contentid)
	{
		ArrayList<String> sqlList = new ArrayList<String>();
        String sql = "insert into news_resources (useragents,name,resourcetype,url,size,rformat,spcode,newformat,fullpath) values " +
        		"('"+newsResources.getUseragents()+"','"+newsResources.getName()+"',"+
			newsResources.getResourcetype()+",'"+newsResources.getUrl()+"',"+newsResources.getSize()+",'"+newsResources.getRformat()+"'," +
				"'"+newsResources.getSpcode()+"','"+newsResources.getNewformat()+"','"+newsResources.getFullpath()+"')";
        sqlList.add(sql);
        log.info(" insertPicResource excute batch sql 1:" +sql);
		sql = "insert into news_contenresource (rid,cid) values ((select LAST_INSERT_ID()),(select cid from news_contenttitle where contentid='"+contentid+"'))";
		sqlList.add(sql);
		log.info(" insertPicResource excute batch sql 2:" +sql);
		try {
			return execTransaction(sqlList);
		} catch (Exception e) {
			log.info(" insertPicResource fail !!!" +e.getMessage());
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean updatePicture(PictureContent one,String channelids)
	{
		BaseBean b = new BaseBean();
		ArrayList<String> sqllist = new ArrayList<String>();
		String contentid = b.ObjectToStringDB(one.getContentid());
		String service_code = b.ObjectToStringDB(one.getServicecode());
		String genre_id = b.ObjectToStringDB(one.getGenre_id());
		String content_name = b.ObjectToStringDB(one.getContent_name());
		String content_keyword = b.ObjectToStringDB(one.getContent_keyword());
		String content_pubdate = b.ObjectToStringDB(one.getContent_pubdate());
		String conten_info = b.ObjectToStringDB(one.getConten_info());
		String Copyright_deadline = b.ObjectToStringDB(one.getCopyright_deadline());
		String content_status = b.ObjectToStringDB(one.getContent_status());
		String pub_company = b.ObjectToStringDB(one.getPub_company()); 
		StringBuffer inPicSql = new StringBuffer("update t_picture set ");
		inPicSql.append("service_code="+service_code+",");
		inPicSql.append("genre_id="+genre_id+",");
		inPicSql.append("content_name="+content_name+",");
		inPicSql.append("content_keyword="+content_keyword+",");
		inPicSql.append("content_pubdate="+content_pubdate+",");
		inPicSql.append("conten_info="+conten_info+",");
		inPicSql.append("content_updateddate=now(),");
		inPicSql.append("Copyright_deadline="+Copyright_deadline+",");
		inPicSql.append("content_status="+content_status+",");
		inPicSql.append("pub_company="+pub_company+" where contentid="+contentid);
		sqllist.add(inPicSql.toString());
		log.info(" updatePicture excute batch sql 1:" +inPicSql.toString());
		inPicSql.delete(0, inPicSql.length());
		String contenttext = "'"+InterUtil.rb.getString("src.pic.title")+":"+one.getContent_name()+"" +
						"@@"+InterUtil.rb.getString("src.pic.time")+":"+new SimpleDateFormat("yyyy-MM-dd").format(new Date())+"" +
						"@@"+InterUtil.rb.getString("src.explain")+":"+one.getConten_info()+"" +
						"@@"+InterUtil.rb.getString("src.source")+":"+one.getPub_company()+"'";
		inPicSql.append("update news_contenttitle set ");
		inPicSql.append("contenttitle="+content_name+",");
		inPicSql.append("contenttext="+contenttext+",");
		inPicSql.append("contentsource="+pub_company+",updatetime=DATE_ADD(updatetime, INTERVAL 10 SECOND) where contentid="+contentid);
		sqllist.add(inPicSql.toString());
		log.info(" updatePicture excute batch sql 2:" +inPicSql.toString());
		inPicSql.delete(0, inPicSql.length());
		//delete old channel content
		inPicSql.append("delete from news_extends_channel_title where cid=(SELECT cid FROM news_contenttitle where contentid="+contentid+")");
		sqllist.add(inPicSql.toString());
		log.info(" updatePicture excute batch sql 3:" +inPicSql.toString());
		String [] channelid=channelids.split(",");
		String tempSql="insert into news_extends_channel_title(channleid,cid,contentstatus,istest,isnew)VALUES(";
		for(int i=0;i<channelid.length;i++)
        {
			//图片内容没有测试状态
			int isNeedTest = 1;
			//是否需要测试,true为是,false为否
//			String needtest = SysProperty.getPrep("news-needtest");
//			if(needtest!=null && needtest.equals("false"))
//				isNeedTest = 0;
			String tmp = tempSql+"(select channleid from news_extends_channel where channel_id='"+channelid[i]+"')," +
			"(SELECT cid FROM news_contenttitle where contentid="+contentid+"),0,"+isNeedTest+",1)";
			sqllist.add(tmp);
			log.info(" updatePicture excute batch sql 4:" +tmp);
        }
		
		try {
			return execTransaction(sqllist);
		} catch (Exception e) {
			log.info(" updatePicture fail !!!" +e.getMessage());
			e.printStackTrace();
			return false;
		}
	}
	
	public List<String> getResFullpathByContentid(String contentid)
	{
		String sql = "select nr.useragents,nr.newformat,nr.fullpath,nr.size from news_resources nr,news_contenttitle nc,news_contenresource ncr " +
				"where nc.contentid='"+contentid+"' and nc.cid=ncr.cid and nr.rid=ncr.rid  ";
		log.info(" getResFullpathByContentid excute sql:" +sql);
		List<String> result = null;
		try {
			List tmpList = queryALLResultSet(sql);
			if(tmpList!=null && tmpList.size()>0)
			{
				result = new ArrayList<String>();
				for(int i=0;i<tmpList.size();i++)
				{
					HashMap hashMap=(HashMap)tmpList.get(i);
					String fullpath = (String)hashMap.get("fullpath");
					String picSize = (String)hashMap.get("useragents");
					String newFormat = (String)hashMap.get("newformat");
					String size = (String)hashMap.get("size");
					fullpath = fullpath.substring(0,fullpath.indexOf("."))+picSize+"."+newFormat+"|"+size;
					result.add(fullpath);
				}
			}
			return result;
		} catch (Exception e) {
			log.info(" getResFullpathByContentid fail !!!" +e.getMessage());
			e.printStackTrace();
			return null;
		}
		
	}
	
	public boolean deletePicFile(String filename,String contentid)
	{
		Object rid = "";
		if(filename != null && !"".equals(filename))
			rid = this.getSingleResult("SELECT a.rid FROM NewsResources a where a.url='"+filename+"'");
		Object cid = this.getSingleResult("select cid from NewsContenttitle where contentid='"+contentid+"'");
		ArrayList<String> sqlList = new ArrayList<String>();
		String sql = "insert into t_resourcedelete (url,format,type) " +
				"(select nr.fullpath,nr.newformat,nr.useragents from news_resources nr where nr.rid="+rid+")";
		sqlList.add(sql);
		log.info(" deletePicFile excute batch sql 1:" +sql);
		sql = "delete from news_contenresource where rid=(select rid from news_resources where rid=" + rid + " and cid=" + cid+")";
		sqlList.add(sql);
		log.info(" deletePicFile excute batch sql 2:" +sql);
		sql = "delete from news_resources where rid="+rid;
		sqlList.add(sql);
		log.info(" deletePicFile excute batch sql 3:" +sql);
		try {
			return execTransaction(sqlList);
		} catch (Exception e) {
			log.info(" deletePicFile fail !!!" +e.getMessage());
			e.printStackTrace();
			return false;
		}
	}
	
	public PictureContent getPcitureById(String contentid)
	{
//		return (PictureContent)get(PictureContent.class, contentid);	//此实体没有映射！！！
		String sql = "SELECT contentid,user_id,service_code,genre_id,content_name,content_keyword,content_pubdate,conten_info,content_updateddate,Copyright_deadline,content_status,audituser,pub_company,cause FROM t_picture tp where 1=1" ;
		if (contentid != null && !contentid.equals("")) {
			sql += " and tp.contentid = '" + contentid +"'";
		}
		DataMap tmp = null;
		try {
			ArrayList tmpp = queryALLResultSet(sql);
			if(tmpp.size()>0)tmp=(DataMap)tmpp.get(0);
		} catch (Exception e) {
			log.info(" getPcitureById fail !!!" +e.getMessage());
            return null;
		}
		PictureContent one = new PictureContent();
		if(tmp == null || tmp.size()==0){
			return null;
		}else{
			one.setContentid(tmp.getMapData("contentid"));
			one.setUser_id(tmp.getMapData("user_id"));
			one.setServicecode(tmp.getMapData("service_code"));
			one.setGenre_id(tmp.getMapData("genre_id"));
			one.setContent_name(tmp.getMapData("content_name"));
			one.setContent_keyword(tmp.getMapData("content_keyword"));
			one.setContent_pubdate(tmp.getMapData("content_pubdate").replace(".0", ""));
			one.setConten_info(tmp.getMapData("conten_info"));
			one.setContent_updateddate(tmp.getMapData("content_updateddate"));
			one.setCopyright_deadline(tmp.getMapData("copyright_deadline").replace(".0", ""));
			one.setContent_status(tmp.getMapData("content_status"));
			one.setAudituser(tmp.getMapData("audituser"));
			one.setPub_company(tmp.getMapData("pub_company"));
			one.setCause(tmp.getMapData("cause"));
			return one;
		}
	}
	
	public ArrayList getServiceAll(String roleid,String userid)
	{
		String sql = "";
        
        if(roleid != null && !roleid.equals(""))
        {
	        sql += "SELECT p.serviceid,p.servicename,p.service_code,p.price,p.price_type,p.servicetype,p.access_no,p.port_type,p.content,p.content_type,p.processClass,p.carrier,p.spnumber,p.order_success_msg,p.ctrmand_cmd,p.ctrmand_no,p.starttime" +
	        		" FROM t_service_info p,t_user_prices up,t_user tu where p.service_code=up.service_code and " +
				"up.employee_id=tu.employee_id and tu.user_id='"+userid+"'";
        }
        else if(roleid == null || roleid.equals(""))
        {
        	sql = "select p.serviceid,p.servicename,p.service_code,p.price,p.price_type,p.servicetype,p.access_no,p.port_type,p.content,p.content_type,p.processClass,p.carrier,p.spnumber,p.order_success_msg,p.ctrmand_cmd,p.ctrmand_no,p.starttime" +
        			" from t_service_info p ";
        }
        try {
            return queryALLResultSet(sql);
        } catch (Exception e) {
        	log.info(" getPriceAll fail !!!" +e.getMessage());
            return null;
        }
	}
	
	public String getSpcodeByUserId(String userid)
	{
		String sql = "select te.sp_code from t_employee te, t_user tu where tu.employee_id=te.employee_id and tu.user_id='" + userid + "'";
		String s = "";
		try {
			s = queryOne(sql);
		} catch (Exception e) {
			log.info(" getSpcodeByUserId excute sql : " +sql);
			log.info(" getSpcodeByUserId fail !!!" +e.getMessage());
			e.printStackTrace();
			return "nospcode";
		}
		return s;
	}
	
	public List<TGenre> getGenreAll()
	{
		String hql = "SELECT tg FROM TGenre tg " ;
		//open
			hql+=	"where tg.conentType='1'" ;
		return getQueryList(hql);
	}
	
	//未选中的（编辑时显示在左侧的），已加权限
	public ArrayList findNotChooseChannel(String content_id, DataMap hashMap) {
		Object roleId = hashMap.get("role_id");
		Object userId = hashMap.get("user_id");
		String userids = (String)hashMap.get("userids");
		//root
		String findSql = "select tc.channel_id,tc.channel_name from t_channels tc WHERE 1=1 " +
				//will open
				" and status='"+BookConfig.channel_status_enable+"' " +
				" and channel_type='"+BookConfig.channel_type_image+"' " + 
				" AND (tc.function_name='LAST' OR tc.function_name='CHANNELSONG') ";
		//normal user
		if(roleId != null && !roleId.equals(""))
        {
			findSql = "select tc.channel_id,tc.channel_name from t_channels tc,t_user_channels uc WHERE tc.channel_id=uc.channel_id " +
            		" and status='"+BookConfig.channel_status_enable+"'" +
            		" and channel_type='"+BookConfig.channel_type_image+"' " + 
            		" and (function_name='"+BookConfig.channel_last+"' OR function_name='"+BookConfig.channel_song+"')  " +
            		" and uc.employee_id=(SELECT employee_id FROM t_user where user_id='" + userId + "')";
        }else if(userids!=null)
        {
			findSql = "select DISTINCT tc.channel_id,tc.channel_name from t_channels tc,t_user_channels uc WHERE tc.channel_id=uc.channel_id " +
		    		" and status='"+BookConfig.channel_status_enable+"'" +
		    		" and channel_type='"+BookConfig.channel_type_image+"' " + 
		    		" and (function_name='"+BookConfig.channel_last+"' OR function_name='"+BookConfig.channel_song+"')  " +
		    		" and uc.employee_id in (SELECT employee_id FROM t_user where user_id in (" + userids + "))";
        }
		findSql = findSql + " and tc.channel_id not in (select tc.channel_id from t_channels tc,news_extends_channel nec," +
				"news_extends_channel_title nct,news_contenttitle nc where nc.contentid='"+content_id+"' and nc.cid=nct.cid and " +
						"nec.channel_id=tc.channel_id and nec.channleid=nct.channleid )";
		try {
			return queryALLResultSet(findSql.toString());
		} catch (Exception e) {
			log.info(" findNotChooseChannel fail !!!" +e.getMessage());
			return null;
		}
	}
	
	public ArrayList findSelectChannelsById(String content_id) {
		StringBuffer findSql = new StringBuffer("");
		findSql.append("select tc.channel_id,tc.channel_name from t_channels tc,news_extends_channel nec,news_extends_channel_title nct,news_contenttitle nc where 1=1 ");
		findSql.append(" and nc.contentid='" + content_id + "'");
		findSql.append(" and nc.cid=nct.cid ");
		findSql.append(" and nec.channel_id=tc.channel_id ");
		findSql.append(" and nec.channleid=nct.channleid ");
		//will open
//		findSql.append(" and status='"+BookConfig.channel_status_enable+"' ");
//		findSql.append(" AND (tc.function_name='"+BookConfig.channel_last+"' OR tc.function_name='"+BookConfig.channel_song+"') ");
        try {
			return queryALLResultSet(findSql.toString());
		} catch (Exception e) {
			log.info(" findSelectChannelsById fail !!!" +e.getMessage());
            return null;
		}
	}
	
	public List getChannelsbyPicture(String contentid)
	{
		String sql = "select tc.channel_id,tc.channel_name from news_contenttitle nc,t_channels tc,news_extends_channel_title nect,news_extends_channel nec " +
		"where nc.contentid='"+contentid+"' and nc.cid=nect.cid and nect.channleid=nec.channleid and tc.channel_id=nec.channel_id";
		try {
			return queryALLResultSet(sql);
		} catch (Exception e) {
			log.info(" getChannelsbyPicture fail !!!" +e.getMessage());
			return null;
		}
	}
	
	public boolean disablePicture(String contentids)
	{
		String cidstr = this.getCidStrForContentidGLobal(contentids);
		ArrayList<String> sqlList = new ArrayList<String>();
		String sql = "update t_picture set content_status='disable' where contentid in ("+contentids+")";
		sqlList.add(sql);
		sql = "update news_extends_channel_title set contentstatus=3 where cid in ("+cidstr+")";
		sqlList.add(sql);
		try {
			return execTransaction(sqlList);
		} catch (Exception e) {
			log.info(" disable picture fail !!!" +e.getMessage());
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean deletePicture(String contentid) {
		ArrayList<String> sqlList = new ArrayList<String>();
		String deleteSql = "delete from t_picture where contentid ='"+contentid+"'";
		sqlList.add(deleteSql);
		deleteSql = "delete from news_extends_channel_title where cid =(select cid from news_contenttitle where contentid='"+contentid+"')";
		sqlList.add(deleteSql);
		deleteSql = "insert into t_resourcedelete (url,format) (select nr.fullpath,nr.newformat from news_resources nr,news_contenresource ncr " +
		"where ncr.cid=(select cid from news_contenttitle where contentid='"+contentid+"') and ncr.rid=nr.rid)";
		sqlList.add(deleteSql);	
		deleteSql = "delete from news_resources where rid in (select rid from news_contenresource where cid=(select cid from news_contenttitle where contentid='"+contentid+"'))";
		sqlList.add(deleteSql);
		deleteSql = "delete from news_contenresource where cid=(select cid from news_contenttitle where contentid='"+contentid+"')";
		sqlList.add(deleteSql);
		deleteSql = "delete from news_contenttitle where contentid='"+contentid+"'";
		sqlList.add(deleteSql);
		try {
			return execTransaction(sqlList);
		} catch (Exception e) {
			log.info(" deletePicture fail !!!" +e.getMessage());
			e.printStackTrace();
			return false;
		}
	}

    //add 1119 select channel update
    public DataMap buildLastChannelMap()
    {
    	String sql = "SELECT channel_id,channel_name,function_name,previous_id FROM t_channels t";
    	HashMap<String,String> childIdAndPreiousId = new HashMap<String,String>();
    	HashMap<String,String> idAndName = new HashMap<String,String>();
    	DataMap result = new DataMap();
    	List chList = null;
    	try {
			chList = this.queryALLResultSet(sql);
		} catch (Exception e) {
			log.info(" buildChChildIdAndPreviousIdMap fail !!!" +e.getMessage());
			e.printStackTrace();
			return null;
		}
		//首先建立两个map准备数据
		for(int i=0;i<chList.size();i++)
		{
			HashMap hashMap = (HashMap)chList.get(i);
			String channel_id = (String)hashMap.get("channel_id");
			String chName = (String)hashMap.get("channel_name");
			String fnName = (String)hashMap.get("function_name");
			String previous_id = (String)hashMap.get("previous_id");
			//先将id和name装入map
			idAndName.put(channel_id, chName);
			//channel如果是normal或者first,就装入map,key是channel_id,value是
			if(fnName.equals(BookConfig.channel_first) || fnName.equals(BookConfig.channel_normal))
			{
				childIdAndPreiousId.put(channel_id, previous_id);
			}
		}
		//build map,key:channleid value:previousid+name
		for(int i=0;i<chList.size();i++)
		{
			HashMap hashMap = (HashMap)chList.get(i);
			String channel_id = (String)hashMap.get("channel_id");
			String chName = (String)hashMap.get("channel_name");
			String fnName = (String)hashMap.get("function_name");
			String previous_id = (String)hashMap.get("previous_id");
			//如果是normal或者first,就装入map,key是channel_id,value是
			if(fnName.equals(BookConfig.channel_last) || fnName.equals(BookConfig.channel_song))
			{
				String previousChName = idAndName.get(previous_id);
				chName = chName+"="+previousChName;
				chName = getValue(childIdAndPreiousId,idAndName,previous_id,chName);
				//最上一级的频道名称必然是news,把news过滤掉
				chName = chName.substring(0,chName.lastIndexOf("="));
				String[] tmp = chName.split("=");
				String value = "";
				for(int j=tmp.length-1;j>=0;j--)
				{
					value=value+tmp[j]+"--";
				}
				value = value.substring(0,value.length()-2);
				result.put(channel_id, value);
			}
		}
    	return result;
    }
    
    private String getValue(HashMap<String,String> idmap,HashMap<String,String> namemap,String key,String result)
    {
    	String parentid = idmap.get(key);
    	String parentname = namemap.get(parentid);
    	if((key != null && parentid!=null) && !parentid.equals(key))
    	{
    		result = result+"="+parentname;
    		return getValue(idmap,namemap,parentid,result);
    	}
    	else
    	{
    		return result;
    	}
    }

	public ArrayList<DataMap> findAllLastChannel(DataMap hashMap) {
		Object roleId = hashMap.get("role_id");
		Object userId = hashMap.get("user_id");
		String userids = (String)hashMap.get("userids");
		//root
		String findSql = "select  ch.channel_id  ,ch.service_code,ch.channel_name,ch.channel_comment ,ch.function_name,ch.STATUS ,ch.ordered ,ch.created_date,ch.previous_id ,ch.primal_id ,ch.LEVEL ,ch.channel_type  ,ch.channel_hot_topics ,ch.channel_classifying_label" +"  from t_channels ch WHERE 1=1 " +
				" and channel_type='"+BookConfig.channel_type_image+"' " +  
				" AND (function_name='"+BookConfig.channel_last+"' OR function_name='"+BookConfig.channel_song+"') ";
		//normal user
		if(roleId != null && !roleId.equals(""))
		{
			findSql = "select  ch.channel_id  ,ch.service_code,ch.channel_name,ch.channel_comment ,ch.function_name,ch.STATUS ,ch.ordered ,ch.created_date,ch.previous_id ,ch.primal_id ,ch.LEVEL ,ch.channel_type  ,ch.channel_hot_topics ,ch.channel_classifying_label" +"  from t_channels ch,t_user_channels uc WHERE ch.channel_id=uc.channel_id " +
					" and channel_type='"+BookConfig.channel_type_image+"' " + 
		    		" and (function_name='"+BookConfig.channel_last+"' OR function_name='"+BookConfig.channel_song+"')  " +
		    		" and uc.employee_id=(SELECT employee_id FROM t_user where user_id='" + userId + "')";
		}else if(userids!=null){
			findSql = "select DISTINCT  ch.channel_id  ,ch.service_code,ch.channel_name,ch.channel_comment ,ch.function_name,ch.STATUS ,ch.ordered ,ch.created_date,ch.previous_id ,ch.primal_id ,ch.LEVEL ,ch.channel_type  ,ch.channel_hot_topics ,ch.channel_classifying_label" +"  from t_channels ch,t_user_channels uc WHERE ch.channel_id=uc.channel_id " +
	    		" and status='"+BookConfig.channel_status_enable+"'" +
				" and channel_type='"+BookConfig.channel_type_image+"' " + 
	    		" and (function_name='"+BookConfig.channel_last+"' OR function_name='"+BookConfig.channel_song+"')  " +
	    		" and uc.employee_id in (SELECT employee_id FROM t_user where user_id in (" + userids + "))";
		}
		try {
			return this.queryALLResultSet(findSql.toString());
		} catch (Exception e) {
			logger.error(" findAllLastChannel fail !!!" +e.getMessage());
			return null;
		}
	}
	
    //add for multi catalog
    public ArrayList getChannelPlatform()
    {
    	ArrayList result = new ArrayList();
    	//1 取得所有生效的catalog对应的catalogchannelid
    	String sql = "SELECT ncc.channelid,ncp.mplatform,ncp.magent,ncp.mserialnum,ncp.mlocale,ncp.catlogid" +
    			" FROM news_catlog nc left join news_catlog_platform ncp on ncp.catlogid=nc.catlogid," +
    			"news_catlog_channel ncc where nc.isnowpublish in (1,2) and nc.catlogid=ncc.catlogid";
    	ArrayList catalogChannelidAndPlatformList = new ArrayList();
    	try {
    		catalogChannelidAndPlatformList = this.queryALLResultSet(sql);
    		if(catalogChannelidAndPlatformList.size()>0)
    		{
	    		for(int i=0;i<catalogChannelidAndPlatformList.size();i++)
	    		{
	    			DataMap lastChannelAndPlatform = new DataMap();
	    			HashMap hashmap = (HashMap)catalogChannelidAndPlatformList.get(i);
	    			String catalogChannelid = (String)hashmap.get("channelid");
	    			String platforminfo = InterUtil.rb.getString("src.gameplatform")+hashmap.get("mplatform")+"."+
	    			InterUtil.rb.getString("src.gamedistributor")+hashmap.get("magent")+"."+InterUtil.rb.getString("src.clientsn")+
	    			hashmap.get("mserialnum")+"."+InterUtil.rb.getString("src.languagearea")+hashmap.get("mlocale");
	    			sql = "SELECT nc.channel_id FROM news_buttons nb,news_extends_firstchannel_buttons nfb,news_contenttype nc where " +
	    					"nfb.channelid="+catalogChannelid+" and nc.newscontenttype=nb.buttoncontenttype and nfb.buttonid=nb.buttonid";
	    			ArrayList tchannelIdList = this.queryALLResultSet(sql);
	    			if(tchannelIdList.size()>0)
	    			{
	    				//首先要判断结果集里的channel是否最后一级
	    				for(int j=0;j<tchannelIdList.size();j++)
	    				{
	    					DataMap tmpMap = (DataMap)tchannelIdList.get(j);
	    					String tch_id = tmpMap.getMapData("channel_id");
	    					if(tch_id == null || tch_id.equals(""))
	    						continue;
	    					sql = "select function_name from t_channels where channel_id='"+tch_id+"'";
	    					String functionname = this.queryOne(sql);
	    					//if最后一级 放到map里 key是最后一级的id value是platforminfo
	    					if(functionname.toUpperCase().equals("LAST") || functionname.toUpperCase().equals("CHANNELSONG"))
	    						lastChannelAndPlatform.put(tch_id, platforminfo);
	    					//不是 递归查询
	    					else
	    						buildLastChannelAndPlatform(tch_id,platforminfo,lastChannelAndPlatform);
	    				}
	    			}
	    			result.add(lastChannelAndPlatform);
	    		}
    		}
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return result;
    }
    
    private void buildLastChannelAndPlatform(String tch_id,String platforminfo,DataMap lastChannelAndPlatform)
    {
    	String sql = "SELECT channel_id,function_name FROM t_channels where previous_id='"+tch_id+"'";
    	try {
			ArrayList chList = this.queryALLResultSet(sql);
			for(int i=0;i<chList.size();i++)
			{
				HashMap hashMap = (HashMap)chList.get(i);
				String fnName = (String)hashMap.get("function_name");
				String channel_id = (String)hashMap.get("channel_id");
				if(fnName.toUpperCase().equals("LAST") || fnName.toUpperCase().equals("CHANNELSONG"))
					lastChannelAndPlatform.put(channel_id, platforminfo);
				else
					buildLastChannelAndPlatform(channel_id,platforminfo,lastChannelAndPlatform);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
    }
}